-- exec SP_TRANSIT_LOTS_REPORT '0','0','0','08/01/2005','0','0','0','0','0'
alter     PROCEDURE SP_TRANSIT_LOTS_REPORT
 	
 	@PROCESS_STEP_IDS 	VARCHAR	(100),
	@BASEPRODUCTID		VARCHAR	(10),	
	@PROCESS_TYPE_NAME 	VARCHAR (20),
	@FROM_DATE 		VARCHAR (10),
	@TO_DATE 		VARCHAR (10),
	@FROM_LOCATION 		VARCHAR	(20),
	@TO_LOCATION 		VARCHAR	(20),
	@REPORT_TYPE 		VARCHAR (20),
 	@DEFAULTPROMPT 		VARCHAR	(20)
 AS
 BEGIN
 	DECLARE @LOT_NUMBER 		VARCHAR(50)
	DECLARE @PART_NUMBER 		VARCHAR(50)
	DECLARE @PO_NUMBER 		VARCHAR(50)
	DECLARE @SO_NUMBER 		VARCHAR(50)
	DECLARE @INVOICE 		VARCHAR(50)
	DECLARE @RS_PROCESS_TYPE_NAME 	VARCHAR(20)
	DECLARE @PREVIOUS_LOC 		VARCHAR(50)
	DECLARE @NEXT_LOC 		VARCHAR(50)
	DECLARE @SHIP_DATE 		DATETIME 
	DECLARE @SHIP_QTY 		INT
	DECLARE @PS_TYPE_ID 		INT 
	DECLARE @SHIPPEDLOT_ID 		INT 
	DECLARE @MAWB 			VARCHAR(50)
	DECLARE @HAWB 			VARCHAR(50)
	DECLARE @FLT 			VARCHAR(50)
	DECLARE @ETD 			VARCHAR(50)
	DECLARE @ETA  			VARCHAR(50) 
	DECLARE @PROCESS_NAME 		VARCHAR(50) 
	DECLARE @BASEPRODUCTNAME 	VARCHAR(50) 
	DECLARE @VENDOR_LOT 		VARCHAR(300)
	DECLARE @TEMP_PROCESSSTEP_ID 	INT
	DECLARE @IS_HOLD 		BIT
	DECLARE @WAFER_ID 		VARCHAR(100)
	DECLARE @VENDOR_ID 		INT
	DECLARE @LOCATION_ID 		INT
	DECLARE @PRODCUT_DESCRIPTION 	VARCHAR(100)
	DECLARE @MAX_SHIP_DATE 		DATETIME
	DECLARE @MIN_SHIP_DATE 		DATETIME
	DECLARE @REL_DATE_TO 		DATETIME
	DECLARE @REL_DATE_FROM 		DATETIME
	DECLARE @STATUS 		VARCHAR(50)

	DECLARE @PROCESS_STEPS_TABLE TABLE( PROCESSSTEP_ID INT )
 	 	 	 		
 	DECLARE @RESULTS TABLE (LOT_NUMBER VARCHAR(50),PART_NUMBER VARCHAR(50),PO_NUMBER VARCHAR(50),SO_NUMBER VARCHAR(50),INVOICE VARCHAR(50),
 				PREVIOUS_LOC VARCHAR(50),NEXT_LOC VARCHAR(50),SHIP_DATE DATETIME, SHIP_QTY INT,PS_TYPE_ID INT, 
				MAWB VARCHAR(50),HAWB VARCHAR(50),FLT VARCHAR(50),ETD VARCHAR(50),ETA  VARCHAR(50), PROCESS_NAME VARCHAR(50), BASEPRODUCTNAME VARCHAR(50), VENDOR_LOT VARCHAR(300),IS_HOLD BIT,WAFER_ID VARCHAR(100),VENDOR_ID INT,LOCATION_ID INT,PRODCUT_DESCRIPTION VARCHAR(100),STATUS VARCHAR(50) )

	SET @MAX_SHIP_DATE =   GETDATE()
	SET @MIN_SHIP_DATE = DATEADD(M,-3, GETDATE())
	
	IF ( @FROM_DATE = '0') AND (@TO_DATE = '0') 
	BEGIN
		SET @REL_DATE_TO = @MAX_SHIP_DATE
		SET @REL_DATE_FROM = @MIN_SHIP_DATE
	END
	ELSE IF (@FROM_DATE <> '0') AND (@TO_DATE <> '0') 
	BEGIN
		SET @REL_DATE_TO = @TO_DATE
		SET @REL_DATE_FROM = @FROM_DATE
	END	
	ELSE IF (@FROM_DATE = '0') AND (@TO_DATE <> '0') 
	BEGIN
		SET @REL_DATE_TO = @TO_DATE
		SET @REL_DATE_FROM = @MIN_SHIP_DATE
	END 
 	ELSE IF (@FROM_DATE <> '0') AND (@TO_DATE = '0') 
	BEGIN
		SET @REL_DATE_TO = @MAX_SHIP_DATE
		SET @REL_DATE_FROM = @FROM_DATE	
	END
	ELSE
	BEGIN
		SET @REL_DATE_TO = @MAX_SHIP_DATE
		SET @REL_DATE_FROM = @MIN_SHIP_DATE
	END


	IF(@PROCESS_STEP_IDS <> '0')
	BEGIN
		INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID )  SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @PROCESS_STEP_IDS )
	END
	ELSE IF (@PROCESS_STEP_IDS = '0')
	BEGIN
		IF( @BASEPRODUCTID <> '0' AND @PROCESS_TYPE_NAME <> '0' )
		BEGIN
			INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID ) SELECT P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P_PARENT_PRODUCT_ID = @BASEPRODUCTID AND PS_TYPE_NAME = @PROCESS_TYPE_NAME
		END
		ELSE IF( @BASEPRODUCTID = '0' AND @PROCESS_TYPE_NAME <> '0' )
		BEGIN
			INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID ) SELECT P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID  AND PS_TYPE_NAME = @PROCESS_TYPE_NAME
		END
		ELSE IF( @BASEPRODUCTID <> '0' AND @PROCESS_TYPE_NAME = '0' )
		BEGIN
			INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID ) SELECT P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID  AND P_PARENT_PRODUCT_ID = @BASEPRODUCTID
		END
		ELSE IF( @BASEPRODUCTID = '0' AND @PROCESS_TYPE_NAME = '0' )
		BEGIN
			INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID ) SELECT P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID  
			--select * from  @PROCESS_STEPS_TABLE
		END
	END
		
 		DECLARE @TRANSITCURSOR CURSOR
	
		IF(@FROM_LOCATION = '0' AND @TO_LOCATION = '0'  )
		BEGIN
			SET @TRANSITCURSOR = CURSOR FOR
			SELECT LT.LOT_NUMBER , PRO.PART_NUMBER , TLT.TLT_CUSTOMER_PO , TLT.TLT_CUSTOMER_SO,
			TLT.TLT_INVOICE_NUMBER , PS.PS_TYPE_NAME, VEN.V_NAME , TLT.TLT_NEXTLOCATION, TLT.TLT_SHIP_DATE, TLT.TLT_SHIP_QTY,
			TLT.TLT_MAWB_NUMBER , TLT.TLT_HAWB_NUMBER , TLT.TLT_FLIGHT_NUMBER , TLT.TLT_ETD , TLT.TLT_ETA, PS.PS_TYPE, LT.PROCESSSTEP_ID , TLT.TLT_VENDOR_LOT_NUMBER, ISNULL(LT.IS_HOLD, 1),TLT.WAFER_ID,VEN.VENDOR_ID,LOC.LOCATION_ID,PRO.P_COMMENTS,SL.STATUS
			FROM LOT_TABLE LT , SHIPPED_LOT SL , TRANSIT_LOT_TABLE TLT , PRODUCT PRO , PROCESS_STEP PS, LOCATION LOC, VENDOR VEN
			WHERE   LT.PROCESSSTEP_ID = PRO.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID IN( SELECT PROCESSSTEP_ID FROM @PROCESS_STEPS_TABLE ) AND
			LT.LOT_ID = SL.LOT_ID AND SL.SHIPPEDLOT_ID = TLT.SHIPPEDLOT_ID /*AND SL.STATUS = 'TRANSIT' */AND LT.LOCATION_ID = LOC.LOCATION_ID AND LOC.VENDOR_ID = VEN.VENDOR_ID
			AND TLT_SHIP_DATE <= @REL_DATE_TO AND TLT_SHIP_DATE >= @REL_DATE_FROM
		END
		ELSE IF(@FROM_LOCATION <> '0' AND @TO_LOCATION = '0'  )
		BEGIN
			
			SET @TRANSITCURSOR = CURSOR FOR
			SELECT LT.LOT_NUMBER , PRO.PART_NUMBER , TLT.TLT_CUSTOMER_PO , TLT.TLT_CUSTOMER_SO,
			TLT.TLT_INVOICE_NUMBER , PS.PS_TYPE_NAME, VEN.V_NAME , TLT.TLT_NEXTLOCATION, TLT.TLT_SHIP_DATE, TLT.TLT_SHIP_QTY,
			TLT.TLT_MAWB_NUMBER , TLT.TLT_HAWB_NUMBER , TLT.TLT_FLIGHT_NUMBER , TLT.TLT_ETD , TLT.TLT_ETA, PS.PS_TYPE, LT.PROCESSSTEP_ID , TLT.TLT_VENDOR_LOT_NUMBER, ISNULL(LT.IS_HOLD, 1),TLT.WAFER_ID,VEN.VENDOR_ID,LOC.LOCATION_ID,PRO.P_COMMENTS,SL.STATUS
			FROM LOT_TABLE LT , SHIPPED_LOT SL , TRANSIT_LOT_TABLE TLT , PRODUCT PRO , PROCESS_STEP PS, LOCATION LOC, VENDOR VEN
			WHERE   LT.PROCESSSTEP_ID = PRO.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID IN( SELECT PROCESSSTEP_ID FROM @PROCESS_STEPS_TABLE ) AND
			LT.LOT_ID = SL.LOT_ID AND SL.SHIPPEDLOT_ID = TLT.SHIPPEDLOT_ID /*AND SL.STATUS = 'TRANSIT'*/ AND LT.LOCATION_ID = LOC.LOCATION_ID AND LOC.VENDOR_ID = VEN.VENDOR_ID
			AND LOC.LOCATION_ID = @FROM_LOCATION AND TLT_SHIP_DATE <= @REL_DATE_TO AND TLT_SHIP_DATE >= @REL_DATE_FROM
		END
		ELSE IF(@FROM_LOCATION = '0' AND @TO_LOCATION <> '0'  )
		BEGIN
			SET @TRANSITCURSOR = CURSOR FOR
			SELECT LT.LOT_NUMBER , PRO.PART_NUMBER , TLT.TLT_CUSTOMER_PO , TLT.TLT_CUSTOMER_SO,
			TLT.TLT_INVOICE_NUMBER , PS.PS_TYPE_NAME, VEN.V_NAME , TLT.TLT_NEXTLOCATION, TLT.TLT_SHIP_DATE, TLT.TLT_SHIP_QTY,
			TLT.TLT_MAWB_NUMBER , TLT.TLT_HAWB_NUMBER , TLT.TLT_FLIGHT_NUMBER , TLT.TLT_ETD , TLT.TLT_ETA, PS.PS_TYPE, LT.PROCESSSTEP_ID , TLT.TLT_VENDOR_LOT_NUMBER, ISNULL(LT.IS_HOLD, 1),TLT.WAFER_ID,VEN.VENDOR_ID,LOC.LOCATION_ID,PRO.P_COMMENTS,SL.STATUS
			FROM LOT_TABLE LT , SHIPPED_LOT SL , TRANSIT_LOT_TABLE TLT , PRODUCT PRO , PROCESS_STEP PS, LOCATION LOC, VENDOR VEN
			WHERE   LT.PROCESSSTEP_ID = PRO.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID IN( SELECT PROCESSSTEP_ID FROM @PROCESS_STEPS_TABLE ) AND
			LT.LOT_ID = SL.LOT_ID AND SL.SHIPPEDLOT_ID = TLT.SHIPPEDLOT_ID /*AND SL.STATUS = 'TRANSIT'*/ AND LT.LOCATION_ID = LOC.LOCATION_ID AND LOC.VENDOR_ID = VEN.VENDOR_ID
			/*AND TLT.TLT_NEXTLOCATION = @TO_LOCATION*/AND TLT_SHIP_DATE <= @REL_DATE_TO AND TLT_SHIP_DATE >= @REL_DATE_FROM
		END
		ELSE IF(@FROM_LOCATION <> '0' AND @TO_LOCATION <> '0'  )
		BEGIN
			SET @TRANSITCURSOR = CURSOR FOR
			SELECT LT.LOT_NUMBER , PRO.PART_NUMBER , TLT.TLT_CUSTOMER_PO , TLT.TLT_CUSTOMER_SO,
			TLT.TLT_INVOICE_NUMBER , PS.PS_TYPE_NAME, VEN.V_NAME , TLT.TLT_NEXTLOCATION, TLT.TLT_SHIP_DATE, TLT.TLT_SHIP_QTY,
			TLT.TLT_MAWB_NUMBER , TLT.TLT_HAWB_NUMBER , TLT.TLT_FLIGHT_NUMBER , TLT.TLT_ETD , TLT.TLT_ETA, PS.PS_TYPE, LT.PROCESSSTEP_ID , TLT.TLT_VENDOR_LOT_NUMBER, ISNULL(LT.IS_HOLD, 1),TLT.WAFER_ID,VEN.VENDOR_ID,LOC.LOCATION_ID,PRO.P_COMMENTS,SL.STATUS
			FROM LOT_TABLE LT , SHIPPED_LOT SL , TRANSIT_LOT_TABLE TLT , PRODUCT PRO , PROCESS_STEP PS, LOCATION LOC, VENDOR VEN
			WHERE   LT.PROCESSSTEP_ID = PRO.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID IN( SELECT PROCESSSTEP_ID FROM @PROCESS_STEPS_TABLE ) AND
			LT.LOT_ID = SL.LOT_ID AND SL.SHIPPEDLOT_ID = TLT.SHIPPEDLOT_ID /*AND SL.STATUS = 'TRANSIT'*/ AND LT.LOCATION_ID = LOC.LOCATION_ID AND LOC.VENDOR_ID = VEN.VENDOR_ID
			AND LOC.LOCATION_ID = @FROM_LOCATION /*AND TLT.TLT_NEXTLOCATION = @TO_LOCATION*/ AND TLT_SHIP_DATE <= @REL_DATE_TO AND TLT_SHIP_DATE >= @REL_DATE_FROM
		END

	OPEN @TRANSITCURSOR
	FETCH NEXT FROM @TRANSITCURSOR INTO @LOT_NUMBER, @PART_NUMBER , @PO_NUMBER , @SO_NUMBER,  @INVOICE , @RS_PROCESS_TYPE_NAME, @PREVIOUS_LOC , @NEXT_LOC , @SHIP_DATE, @SHIP_QTY,@MAWB,@HAWB,@FLT,@ETD,@ETA,@PROCESS_NAME, @TEMP_PROCESSSTEP_ID , @VENDOR_LOT, @IS_HOLD,@WAFER_ID,@VENDOR_ID,@LOCATION_ID,@PRODCUT_DESCRIPTION,@STATUS
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @BASEPRODUCTNAME = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = ( SELECT P_PARENT_PRODUCT_ID FROM PRODUCT WHERE PROCESSSTEP_ID = @TEMP_PROCESSSTEP_ID )
		
 		SET @PS_TYPE_ID = 
		CASE 
			WHEN @RS_PROCESS_TYPE_NAME='FAB' THEN 1
			WHEN @RS_PROCESS_TYPE_NAME='WAFER STORE' THEN 2
			WHEN @RS_PROCESS_TYPE_NAME='SORT' THEN 3
			WHEN @RS_PROCESS_TYPE_NAME='DIE BANK' THEN 4
			WHEN @RS_PROCESS_TYPE_NAME='ASSEMBLY' THEN 5
			WHEN @RS_PROCESS_TYPE_NAME='TEST' THEN 6
			WHEN @RS_PROCESS_TYPE_NAME='CLASS STORE' THEN 7
			WHEN @RS_PROCESS_TYPE_NAME='MARK & PACK' OR @PROCESS_NAME = 'MARK &AMP; PACK' THEN 8
			WHEN @RS_PROCESS_TYPE_NAME='FINISHED GOODS' OR @PROCESS_NAME = 'FG' THEN 9
			ELSE 0
		END

		IF (@STATUS = 'TRANSIT')
		BEGIN
			SET @STATUS = 'Transit'
		END
		ELSE IF (@STATUS = 'CLOSE')
		BEGIN
			SET @STATUS = 'Close'
		END
		
		IF @SHIP_QTY > 0 
		BEGIN
 			INSERT INTO @RESULTS(LOT_NUMBER,PART_NUMBER,PO_NUMBER,SO_NUMBER,INVOICE,PREVIOUS_LOC,NEXT_LOC,SHIP_DATE,SHIP_QTY,MAWB,HAWB,FLT,ETD,ETA, PROCESS_NAME, BASEPRODUCTNAME , VENDOR_LOT, IS_HOLD, PS_TYPE_ID,WAFER_ID,VENDOR_ID,LOCATION_ID,PRODCUT_DESCRIPTION,STATUS )
			VALUES (@LOT_NUMBER,@PART_NUMBER,@PO_NUMBER,@SO_NUMBER,@INVOICE,@PREVIOUS_LOC,@NEXT_LOC,CONVERT(DATETIME,@SHIP_DATE),@SHIP_QTY,@MAWB,@HAWB,@FLT,@ETD,@ETA, @RS_PROCESS_TYPE_NAME, @BASEPRODUCTNAME , @VENDOR_LOT, @IS_HOLD, @PS_TYPE_ID,@WAFER_ID,@VENDOR_ID,@LOCATION_ID,@PRODCUT_DESCRIPTION,@STATUS )
 		END

			SET @LOT_NUMBER 		= NULL
			SET @PART_NUMBER 		= NULL
			SET @PO_NUMBER 			= NULL
			SET @SO_NUMBER 			= NULL
			SET @INVOICE 			= NULL
			SET @PREVIOUS_LOC 		= NULL
			SET @NEXT_LOC 			= NULL
			SET @SHIP_DATE 			= NULL
			SET @SHIP_QTY 			= NULL
			SET @MAWB 			= NULL
			SET @HAWB 			= NULL
			SET @FLT 			= NULL
			SET @ETD 			= NULL
			SET @ETA 			= NULL
			SET @RS_PROCESS_TYPE_NAME 	= NULL
			SET @BASEPRODUCTNAME  		= NULL
			SET @VENDOR_LOT 		= NULL
			SET @IS_HOLD 			= NULL
			SET @PS_TYPE_ID 		= NULL
			SET @WAFER_ID  			= NULL
			SET @VENDOR_ID 			= NULL
			SET @LOCATION_ID 		= NULL
			SET @STATUS 			= NULL
		
	FETCH NEXT FROM @TRANSITCURSOR INTO @LOT_NUMBER, @PART_NUMBER , @PO_NUMBER , @SO_NUMBER,  @INVOICE , @RS_PROCESS_TYPE_NAME, @PREVIOUS_LOC , @NEXT_LOC , @SHIP_DATE, @SHIP_QTY,@MAWB,@HAWB,@FLT,@ETD,@ETA,@PROCESS_NAME, @TEMP_PROCESSSTEP_ID , @VENDOR_LOT, @IS_HOLD,@WAFER_ID,@VENDOR_ID,@LOCATION_ID,@PRODCUT_DESCRIPTION,@STATUS 
 	END
 
 	CLOSE @TRANSITCURSOR
 	DEALLOCATE @TRANSITCURSOR
 	 	SELECT * FROM @RESULTS ORDER BY PART_NUMBER,PS_TYPE_ID,SHIP_DATE desc
 		

 END

